﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace TonyNet4V1.SQLServer
{
	/// <summary>
	/// 用ADO.Net对SQL Server数据库操作函数的封装
	/// </summary>
	public class DBOperation : BaseClass
	{
		#region 属性

		/// <summary>
		/// 数据库链接字符串
		/// </summary>
		/// <![CDATA[2014-6-20 15:29:07]]>
		protected string strConn = "";

		#endregion 属性

		#region 构造函数

		public DBOperation(System.Web.UI.Page page)
			: base(page)
		{
			//strConn = "server=(local);database=PX;uid=sa;pwd='sa'";
			strConn = "server=(local);database=PXExpert;uid=sa;pwd='sa123!'";
			//strConn = "server=localhost\\TONY;database=PX;uid=sa;pwd='sa123!'";
			//strConn = "server=172.16.8.31;database=PX;uid=px;pwd='px'";
		}

		#endregion 构造函数

		#region 实现方法

		/// <summary>
		/// 获得数据库链接对象
		/// <para>如果数据库连接失败时返回null</para>
		/// </summary>
		private SqlConnection GetConnection()
		{
			SqlConnection lconn;
			
			lconn = new SqlConnection();
			try
			{
				lconn.ConnectionString = strConn;
			}
			catch (Exception ex)
			{
				ShowMessageAlert.ShowMessage(page, ex.Message);
				lconn = null;
			}
			finally
			{
				
			}

			return lconn;
		}

		/// <summary>
		/// 执行一个SQL语句，并返回受影响的行数
		/// </summary>
		public int ExecuteSQL(string strSQL)
		{
			SqlConnection conn = new SqlConnection();
			int n = 0;

			try
			{
				conn = GetConnection();
				conn.Open();

				SqlCommand cmd = new SqlCommand(strSQL, conn);
				n = cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				ShowMessageAlert.ShowMessage(page, ex.Message);
			}
			finally
			{
				if (conn != null)
				{
					conn.Dispose();
				}

			}
			return n;
		}

		/// <summary>
		/// 根据查询条件查询，返回DataTable
		/// <para>如果查不到，返回null</para>
		/// </summary>
		public DataTable Query(string strSQL)
		{
			SqlConnection conn = new SqlConnection();
			DataSet ds = new DataSet();

			try
			{
				conn = GetConnection();
				conn.Open();
				SqlCommand cmd = new SqlCommand(strSQL, conn);
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				da.Fill(ds);
			}
			catch (SqlException ex)
			{
				ShowMessageAlert.ShowMessage(page, ex.Message);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
				}
			}

			if (ds.Tables.Count > 0)
			{
				return ds.Tables[0];
			}
			else
			{
				return null;
			}
		}
		////////////////////////////////////////////////////////////////////////////////////////////////////

		#endregion 实现方法
	}
}